﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.OracleClient;
using Pms.Utils;
using Biblioteca.Utils;
using Biblioteca.DataBase;
using System.ComponentModel;

namespace SERIX.Classes
{
    [DataObject(true)]
    public class CIndicadores
    {
        
        static public DataTable PesquisaporCodigo(string idt_ind, string login)
        {
            string SQL = @"SELECT *
                            FROM  Serix.SRX_INDICADORES 
                            WHERE IDT_IND = :IDT_IND";
            Query qy = null;
            try
            {
                qy = new Query(BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, login, BiblioPMS.Demo), SQL);
                qy.AdicionarParametro(":idt_ind", DbType.String, idt_ind);

                return qy.ObterDataTable();
            }
            catch
            {
                if (qy.EstadoConexao() == ConnectionState.Open)
                {
                    qy.FecharConexao();
                }
                return null;
            }
            finally
            {
                qy.FecharConexao();
            }
        }


        [DataObjectMethod(System.ComponentModel.DataObjectMethodType.Select)]
        static public DataTable PesquisaporClasse(string idt_Clas_ind, string login, bool inativos)
        {
            string SQL = @"SELECT *
                            FROM  Serix.SRX_INDICADORES 
                            WHERE IDT_CLA_IND = :IDT_CLA_IND and STATUS_IND = :STATUS_IND";
            Query qy = null;
            try
            {
                string STATUS_IND = inativos ? "0" : "1";
                qy = new Query(BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, login, BiblioPMS.Demo), SQL);
                qy.AdicionarParametro(":IDT_CLA_IND", DbType.String, idt_Clas_ind);
                qy.AdicionarParametro(":STATUS_IND", DbType.String, STATUS_IND);

                return qy.ObterDataTable();
            }
            catch
            {
                if (qy.EstadoConexao() == ConnectionState.Open)
                {
                    qy.FecharConexao();
                }
                return null;
            }
            finally
            {
                qy.FecharConexao();
            }
        }

        
        static public DataTable ObterTodos(string login)
        {

            OracleCommand command = new OracleCommand();

            string SQL = @"SELECT IDT_IND, DSC_IND FROM Serix.SRX_INDICADORES ORDER BY DSC_IND";

            try
            {
                command.Connection = BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG,login, false);
                command.CommandType = CommandType.Text;
                command.CommandText = SQL;

                DataTable tbl = new DataTable();
                tbl.Load(command.ExecuteReader(CommandBehavior.CloseConnection));
                return tbl;

            }
            catch (Exception e)
            {

                if (e is OracleException)
                    throw new Exception(Biblio.ObterMensagem(((OracleException)e).Code, e.Message));
                else
                    throw new Exception(e.Message + " : " + "");
            }
            finally
            {
                if (command != null && command.Connection.State == ConnectionState.Open)
                    command.Connection.Close();
            }
        }

        /// <summary>
        /// Alterando um Indicadores.
        /// </summary>
        public static void Alterar(string Login, string idt_Cla_ind, string IDT_IND, string DSC_IND, string STATUS_IND)
        {
            OracleCommand command = new OracleCommand();
            //OracleTransaction trans;

            string SQL = @"update Serix.SRX_INDICADORES set 
			                   DSC_IND = :DSC_IND,
                                idt_Cla_ind = :idt_Cla_ind,
                                STATUS_IND = :STATUS_IND
						   where
						       IDT_IND = :IDT_IND ";

            try
            {
                command.Connection = BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, Login, BiblioPMS.Demo);
                //trans = command.Connection.BeginTransaction();
                //command.Transaction = trans;
                command.CommandType = CommandType.Text;
                command.CommandText = SQL;

                command.Parameters.Add(new OracleParameter("IDT_IND", OracleType.Number));
                command.Parameters.Add(new OracleParameter("DSC_IND", OracleType.VarChar));
                command.Parameters.Add(new OracleParameter("STATUS_IND", OracleType.VarChar));
                command.Parameters.Add(new OracleParameter("idt_Cla_ind", OracleType.Number));

                command.Parameters["IDT_IND"].Value = IDT_IND;
                command.Parameters["DSC_IND"].Value = DSC_IND;
                command.Parameters["idt_Cla_ind"].Value = idt_Cla_ind;
                command.Parameters["STATUS_IND"].Value = STATUS_IND;
                command.ExecuteNonQuery();
                command.Parameters.Clear();
                //command.Transaction.Commit();
            }
            catch (Exception e)
            {
                //command.Transaction.Rollback();

                if (e is OracleException)
                    throw new Exception(Biblio.ObterMensagem(((OracleException)e).Code, e.Message));
                else
                    throw new Exception(e.Message + " : " + "");
            }
        }

        /// <summary>
        /// Exclui um  Classes_indicadores.
        /// </summary>
        public static void Excluir(string Login, string IDT_IND)
        {
            Tabela tab = new Tabela("SRX_INDICADORES", BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, Login, BiblioPMS.Demo));
            tab.adicionarFiltro(new Coluna("IDT_IND", DbType.Int32, IDT_IND));
            tab.delete();
        }


        /// <summary>
        /// Inserindo um novo Classes_indicadores.
        /// </summary>
        public static void Inserir(string Login, string idt_Cla_ind, string DSC_IND, string STATUS_IND)
        {
            OracleCommand command = new OracleCommand();
            //OracleTransaction trans;

            string SQL = @"insert into Serix.SRX_INDICADORES
			                  ( IDT_IND, DSC_IND, idt_Cla_ind, STATUS_IND
)
						   values
						      ( (select nvl(max(IDT_IND),0) from Serix.SRX_INDICADORES)+1
                                , :DSC_IND
                                , :idt_Cla_ind
                                , :STATUS_IND)";

            try
            {
                command.Connection = BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, Login, BiblioPMS.Demo);
                //trans = command.Connection.BeginTransaction();
                //command.Transaction = trans;
                command.CommandType = CommandType.Text;
                command.CommandText = SQL;

                command.Parameters.Add(new OracleParameter("idt_Cla_ind", OracleType.Number));
                command.Parameters.Add(new OracleParameter("DSC_IND", OracleType.VarChar));
                command.Parameters.Add(new OracleParameter("STATUS_IND", OracleType.VarChar));

                command.Parameters["idt_Cla_ind"].Value = idt_Cla_ind;
                command.Parameters["DSC_IND"].Value = DSC_IND;
                command.Parameters["STATUS_IND"].Value = STATUS_IND;
                command.ExecuteNonQuery();
                command.Parameters.Clear();
                //command.Transaction.Commit();
            }
            catch (Exception e)
            {
                //command.Transaction.Rollback();

                if (e is OracleException)
                    throw new Exception(Biblio.ObterMensagem(((OracleException)e).Code, e.Message));
                else
                    throw new Exception(e.Message + " : " + "");
            }
        }
        [DataObjectMethod(System.ComponentModel.DataObjectMethodType.Select)]
        public static DataTable ObterIndicadores(string IDT_PROG, string IDT_ACAO, string ano_Plo, string login)
        {
            if (string.IsNullOrEmpty(IDT_PROG) || string.IsNullOrEmpty(IDT_ACAO) || string.IsNullOrEmpty(ano_Plo))
                return new DataTable();

            string sql = @"select prog.idt_ind, ind.dsc_ind from srx_programacao prog
  join srx_indicadores ind on ind.idt_ind = prog.idt_ind
where ano_prog = :ano_prog and idt_acao = :idt_acao 
  and idt_prog = :idt_prog";
            OracleCommand command = new OracleCommand();
            try
            {
                command.Connection = BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, login, BiblioPMS.Demo);
                //trans = command.Connection.BeginTransaction();
                //command.Transaction = trans;
                command.CommandType = CommandType.Text;
                command.CommandText = sql;

                command.Parameters.Add(new OracleParameter("ano_prog", OracleType.Number));
                command.Parameters.Add(new OracleParameter("idt_acao", OracleType.VarChar));
                command.Parameters.Add(new OracleParameter("idt_prog", OracleType.VarChar));

                command.Parameters["ano_prog"].Value = ano_Plo;
                command.Parameters["idt_acao"].Value = IDT_ACAO;
                command.Parameters["idt_prog"].Value = IDT_PROG;
                
                command.ExecuteNonQuery();
                command.Parameters.Clear();
                DataTable tbl = new DataTable();
                tbl.Load(command.ExecuteReader(CommandBehavior.CloseConnection));
                return tbl;
                //command.Transaction.Commit();
            }
            catch (Exception e)
            {
                if (e is OracleException)
                    throw new Exception(Biblio.ObterMensagem(((OracleException)e).Code, e.Message));
                else
                    throw new Exception(e.Message + " : " + "");
            }
            finally
            {
                if (command.Connection != null && command.Connection.State != ConnectionState.Closed)
                    command.Connection.Close();
            }
        }
                
    }
}
